package com.homework.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.homework.bean.AnswerBean;
import com.homework.bean.CourseBean;
import com.homework.bean.PageInfoBean;
import com.homework.bean.QuestionBean;
import com.homework.bean.UserAnswerBean;
import com.homework.bean.UserAnswerListBean;
import com.homework.bean.UserBean;
import com.homework.bean.UserQuestionBean;
import com.homework.bean.UserQuestionListBean;
import com.homework.service.PageInfoService;
import com.homework.util.connDB;

public class QuestionDao {
	private static QuestionDao questionDao = null;
	private connDB conn = new connDB();
	private ResultSet rs;
	private PageInfoService pageInfoService;
	// sql语句
	private String sql = "";

	public QuestionDao() {
		pageInfoService = new PageInfoService();
	}

	public static QuestionDao getInstance() {
		if (questionDao == null) {
			questionDao = new QuestionDao();
		}
		return questionDao;
	}

	// 问题列表
	public UserQuestionListBean questionShow(CourseBean courseBean,
			PageInfoBean pageInfoBean) throws SQLException {
		// 数据库连接
		sql = "select t_question.*,t_temp.f_username from t_question natural join (select t_user.f_username,t_questionTemp.f_chooseId from t_user natural join (select f_chooseId,f_userId from t_choose where f_courseId = "
				+ courseBean.getCourseId() + ") as t_questionTemp ) as t_temp;";
		rs = conn.executeQuery(sql);

		// --------------分页基本参数获取---------------
		pageInfoBean.setRs(rs);
		pageInfoService.setPageInfoBean(pageInfoBean);

		// 获取UserQuestionListBean
		UserQuestionListBean userQuestionListBean = new UserQuestionListBean();
		for (int i = 0; i < pageInfoBean.getNum(); i++) {
			UserQuestionBean userQuestionBean = new UserQuestionBean();
			QuestionBean questionBean = new QuestionBean();
			UserBean userBean = new UserBean();
			questionBean.setQuestionId(rs.getString("f_questionId"));
			questionBean.setQuestionHead(rs.getString("f_questionHead"));
			questionBean.setCreationTime(rs.getString("f_creationTime"));
			questionBean.setQuestionBody(rs.getString("f_questionBody"));
			questionBean.setQuestionStatus(rs.getString("f_questionStatus"));
			questionBean.setChooseId(rs.getString("f_chooseId"));
			userBean.setUsername(rs.getString("f_username"));
			userQuestionBean.setQuestionBean(questionBean);
			userQuestionBean.setUserBean(userBean);
			userQuestionListBean.getUserQuestionList().add(userQuestionBean);
			rs.next(); // 下一条记录
		}

		rs.close();
		conn.close();
		return userQuestionListBean;
	}

	// 添加问题
	public void questionAdd(UserBean userBean, CourseBean courseBean,
			QuestionBean questionBean) {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置日期格式
		// 数据库连接
		sql = "insert into t_question (f_questionHead,f_creationTime,f_questionBody,f_questionStatus,f_chooseId) values ('"
				+ questionBean.getQuestionHead()
				+ "','"
				+ df.format(new Date())
				+ "','"
				+ questionBean.getQuestionBody()
				+ "',1,(select f_chooseId from t_choose where f_courseId = "
				+ courseBean.getCourseId()
				+ " and f_userId = "
				+ userBean.getUserId() + "));";
		conn.executeUpdate(sql);
		conn.close();
	}

	// 获取问题
	public UserQuestionBean userQuestionGet(QuestionBean questionBean)
			throws SQLException {
		// 数据库连接
		sql = "select t_chooseTemp.*,t_user.f_username from t_user natural join (select t_questionTemp.*,t_choose.f_userId from t_choose natural join (select * from t_question where f_questionId = "
				+ questionBean.getQuestionId()
				+ " and f_questionStatus = 1) as t_questionTemp) as t_chooseTemp;";
		rs = conn.executeQuery(sql);
		UserQuestionBean userQuestionBean = new UserQuestionBean();
		if (rs.next()) {
			UserBean userBean = new UserBean();
			QuestionBean questionBeanTemp = new QuestionBean();
			questionBeanTemp.setQuestionId(rs.getString("f_questionId"));
			questionBeanTemp.setQuestionHead(rs.getString("f_questionHead"));
			questionBeanTemp.setCreationTime(rs.getString("f_creationTime"));
			questionBeanTemp.setQuestionBody(rs.getString("f_questionBody"));
			questionBeanTemp
					.setQuestionStatus(rs.getString("f_questionStatus"));
			questionBeanTemp.setChooseId(rs.getString("f_chooseId"));
			userBean.setUsername(rs.getString("f_username"));
			userQuestionBean.setQuestionBean(questionBeanTemp);
			userQuestionBean.setUserBean(userBean);
		}

		rs.close();
		conn.close();
		return userQuestionBean;
	}

	// 获取回复
	public UserAnswerListBean userAnswerListGet(QuestionBean questionBean,
			PageInfoBean pageInfoBean) throws SQLException {
		// 数据库连接
		sql = "select t_user.f_username,t_chooseTemp.* from t_user natural join (select t_choose.f_userId,t_answer.* from t_choose natural join (select * from t_answer where f_answerStatus = 1 and f_questionId = "
				+ questionBean.getQuestionId()
				+ " ) as t_answer ) as t_chooseTemp;";
		rs = conn.executeQuery(sql);

		// --------------分页基本参数获取---------------
		pageInfoBean.setRs(rs);
		pageInfoService.setPageInfoBean(pageInfoBean);

		// 获取UserAnswerListBean
		UserAnswerListBean userAnswerListBean = new UserAnswerListBean();
		for (int i = 0; i < pageInfoBean.getNum(); i++) {
			UserAnswerBean userAnswerBean = new UserAnswerBean();
			AnswerBean answerBean = new AnswerBean();
			UserBean userBean = new UserBean();
			answerBean.setAnswerId(rs.getString("f_answerId"));
			answerBean.setAnswerCreationTime(rs
					.getString("f_answerCreationTime"));
			answerBean.setAnswerBody(rs.getString("f_answerBody"));
			answerBean.setAnswerStatus(rs.getString("f_answerStatus"));
			answerBean.setChooseId(rs.getString("f_chooseId"));
			answerBean.setQuestionId(rs.getString("f_questionId"));
			userBean.setUsername(rs.getString("f_username"));
			userAnswerBean.setAnswerBean(answerBean);
			userAnswerBean.setUserBean(userBean);
			userAnswerListBean.getUserAnswerList().add(userAnswerBean);
			rs.next(); // 下一条记录
		}

		rs.close();
		conn.close();
		return userAnswerListBean;
	}

	// 提交回复
	public void answerAdd(UserBean userBean, CourseBean courseBean,
			AnswerBean answerBean) {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置日期格式
		// 数据库连接
		sql = "insert into t_answer (f_answerCreationTime,f_answerBody,f_answerStatus,f_chooseId,f_questionId) values ('"
				+ df.format(new Date())
				+ "','"
				+ answerBean.getAnswerBody()
				+ "',1,(select f_chooseId from t_choose where f_userId = "
				+ userBean.getUserId()
				+ " and f_courseId = "
				+ courseBean.getCourseId()
				+ "),"
				+ answerBean.getQuestionId()
				+ ");";
		conn.executeUpdate(sql);
		conn.close();
	}
}
